﻿/*----------------------------------------------------------------
    // Copyright (C) 2009 江苏华招网
    // 版权所有。 
    // 作者：庄金冬 
    //
    // 文件名：DAL_ProcureCatalog.cs
    // 文件功能描述：采购目录数据访问逻辑
    // 创建标识：庄金冬2009-03-22
    //
    // 修改标识：
    // 修改描述：
    //
    // 修改标识：
    // 修改描述：
    //
    // 审核人：
    // 审核时间：
    // 审核说明：
 //----------------------------------------------------------------*/
using System;
using System.Collections.Generic;

using System.Text;
using HZ.DrugTrade.Model;
using System.Data;
using HZ.DrugTrade.Model.Enum;
using System.Data.SqlClient;
namespace HZ.DrugTrade.DAL
{
    /// <summary>
    /// 采购目录数据访问逻辑
    /// </summary>
    public class DAL_ProcureCatalog
    {
        /// <summary>
        /// 获取采购目录
        /// </summary>
        /// <returns>采购目录列表</returns>
        public static IList<Model.View.ProcureCatalog> GetAllProcureCatalog()
        {
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable("SELECT * FROM View_ProcureCatalog_tb ");
            return HZ.Data.DAL_Helper.CommonFillList<Model.View.ProcureCatalog>(dt);
        }


        /// <summary>
        /// 条件查询采购目录信息（有效的【在采购项目有效期内】）
        /// </summary>
        /// <param name="procureCatalog">查询用实体</param>
        /// <param name="currentPage">当前页</param>
        /// <param name="pageSize">每页显示记录数</param>
        /// <param name="totalRecord">（输出参数）总记录数目</param>
        /// <returns>所有采购目录信息</returns>
        public static IList<Model.View.ProcureCatalog> GetProcureCatalogByWhere(HZ.DrugTrade.Model.Query.ProcureCatalog procurecatalog, int currentPage, int pageSize, out int totalRecord)
        {
            string where = HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition<HZ.DrugTrade.Model.Query.ProcureCatalog>(procurecatalog, true);

            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(string.Format("SELECT * FROM View_ProcureCatalog_tb  WHERE 1=1 {0} ", where), pageSize);
            pdes.CurrentPage = currentPage;
            totalRecord = pdes.TotalCount;

            DataTable dt = pdes.GetSpecailPage();
            return HZ.Data.DAL_Helper.CommonFillList<HZ.DrugTrade.Model.View.ProcureCatalog>(dt);
        }
        /// <summary>
        /// 条件查询采购目录信息（有效的【在采购项目有效期内】）医疗机构
        /// add by Meng 2013-04-22 添加医疗机构设置的实际采购价格
        /// </summary>
        /// <param name="procureCatalog">查询用实体</param>
        /// <param name="currentPage">当前页</param>
        /// <param name="pageSize">每页显示记录数</param>
        /// <param name="totalRecord">（输出参数）总记录数目</param>
        /// <returns>所有采购目录信息</returns>
        public static IList<Model.View.ProcureCatalog> GetProcureCatalogByWhere_Hospital(HZ.DrugTrade.Model.Query.ProcureCatalog procurecatalog, string hospitalid, int currentPage, int pageSize, out int totalRecord)
        {
            string where = HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition<HZ.DrugTrade.Model.Query.ProcureCatalog>(procurecatalog, true);

            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(string.Format(@"select * from (SELECT a.*,b.price sjprice,companyid_ps,companyname_ps
                                          FROM View_ProcureCatalog_tb a left join (select * from tb_hospitalprocurecatalog where hospitalid = '{1}') b
                                          on a.procurecatalogid = b.procurecatalogid)  WHERE 1=1 {0} ", where,hospitalid), pageSize);
            pdes.CurrentPage = currentPage;
            totalRecord = pdes.TotalCount;

            DataTable dt = pdes.GetSpecailPage();
            return HZ.Data.DAL_Helper.CommonFillList<HZ.DrugTrade.Model.View.ProcureCatalog>(dt);
        }
        /// <summary>
        /// 导出Table
        /// </summary>
        /// <param name="procurecatalog"></param>
        /// <param name="currentPage"></param>
        /// <param name="pageSize"></param>
        /// <param name="totalRecord"></param>
        /// <returns></returns>
        public static DataTable GetProcureCatalogByWhere1(HZ.DrugTrade.Model.Query.ProcureCatalog procurecatalog, int currentPage, int pageSize, out int totalRecord)
        {
            string where = HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition<HZ.DrugTrade.Model.Query.ProcureCatalog>(procurecatalog, true);

            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(string.Format("SELECT GoodsId,GoodsName,Outlookc,Material,Unit,bidprice,PackCount,case when packcount is null then ''else to_char(to_number(packcount)*bidprice) end as PriceBid ,PackUnit,QualityLevel,CompanyName_SC,CompanyName_TB,case when SubmitStatus=1 then '已提交' else '未提交'end as SubmitStatus,case when CheckStatus=1 then '审核通过' when CheckStatus=0 then '未审核' when CheckStatus=2 then '审核未通过'end as CheckStatus ,NOPASSRESON FROM View_ProcureCatalog_tb WHERE 1=1 {0} ", where), pageSize);
            pdes.CurrentPage = currentPage;
            totalRecord = pdes.TotalCount;

            DataTable dt = pdes.GetSpecailPage();
            return dt;
        }
        /// <summary>
        /// 条件查询采购目录信息（有效的【在采购项目有效期内】）--带区域条件
        /// </summary>
        /// <param name="procureCatalog">查询用实体</param>
        /// <param name="currentPage">当前页</param>
        /// <param name="pageSize">每页显示记录数</param>
        /// <param name="totalRecord">（输出参数）总记录数目</param>
        /// <returns>所有采购目录信息</returns>
        public static IList<Model.View.ProcureCatalog> GetProcureCatalogByWhereArea(string strAreaID, HZ.DrugTrade.Model.Query.ProcureCatalog procurecatalog, int currentPage, int pageSize, out int totalRecord)
        {
            string where = HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition<HZ.DrugTrade.Model.Query.ProcureCatalog>(procurecatalog,true);

            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(string.Format("SELECT * FROM View_AreaProductList  WHERE 1=1 AND AreaID ='" + strAreaID + "' {0} ", where), pageSize);
            pdes.CurrentPage = currentPage;
            totalRecord = pdes.TotalCount;

            DataTable dt = pdes.GetSpecailPage();
            return HZ.Data.DAL_Helper.CommonFillList<HZ.DrugTrade.Model.View.ProcureCatalog>(dt);
        }

        /// <summary>
        /// 根据登陆ID查询价格修改权限
        /// </summary>
        /// <param name="strUserID"></param>
        /// <returns></returns>
        public static DataTable GetTB_ManageOrg(string strUserID)
        {
            string sql = "SELECT * FROM TB_ManageOrg  WHERE 1=1 AND ManageOrgID =(SELECT OrgID FROM TB_Role_User WHERE UserID ='" + strUserID + "')";
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);
            return dt;
        }


        /// <summary>
        /// 中心查看盟市价格
        /// </summary>
        /// <param name="strUserID"></param>
        /// <returns></returns>
        public static DataTable GetAreaProductList(string ProcureCatalogID)
        {
            string sql = "SELECT A.*,B.AreaName FROM TB_AreaProcureCataLog A INNER JOIN TB_Area B ON A.AreaID = B.AreaID  WHERE 1=1 AND ProcurecatalogID = '" + ProcureCatalogID + "'";
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);
            return dt;
        }

        /// <summary>
        /// 盟市价格权限
        /// </summary>
        /// <param name="strUserID"></param>
        /// <returns></returns>
        public static DataTable GetAreaAccessPrice()
        {
            string sql = "SELECT * FROM TB_ManageOrg WHERE ManageOrgID != '1'";
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);
            return dt;
        }

       

        /// <summary>
        /// 条件查询采购目录信息（有效的【在采购项目有效期内】）
        /// </summary>
        /// <param name="procureCatalog">查询用实体</param>
        /// <param name="currentPage">当前页</param>
        /// <param name="pageSize">每页显示记录数</param>
        /// <param name="totalRecord">（输出参数）总记录数目</param>
        /// <returns>所有采购目录信息</returns>
        public static DataTable GetProcureCatalogDTByWhere(HZ.DrugTrade.Model.Query.ProcureCatalog procurecatalog, int currentPage, int pageSize, out int totalRecord)
        {
            string where = HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition<HZ.DrugTrade.Model.Query.ProcureCatalog>(procurecatalog,true);

            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(string.Format("SELECT * FROM View_ProcureCatalog_tb WHERE 1=1 {0} ", where), pageSize);
            pdes.CurrentPage = currentPage;
            totalRecord = pdes.TotalCount;

            return pdes.GetSpecailPage();
        }

        /// <summary>
        /// 条件查询采购目录信息（无效的【不在采购项目有效期内】）
        /// </summary>
        /// <param name="procureCatalog">查询用实体</param>
        /// <param name="currentPage">当前页</param>
        /// <param name="pageSize">每页显示记录数</param>
        /// <param name="totalRecord">（输出参数）总记录数目</param>
        /// <returns>所有采购目录信息</returns>
        public static IList<Model.View.ProcureCatalog> GetInvalidProcureCatalogByWhere(HZ.DrugTrade.Model.Query.ProcureCatalog procurecatalog, int currentPage, int pageSize, out int totalRecord)
        {
            string where = HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition<HZ.DrugTrade.Model.Query.ProcureCatalog>(procurecatalog,true);

            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(string.Format("SELECT * FROM View_ProcureCatalog_tb WHERE ProcureStartTime>sysdate OR ProcureEndTime<sysdate {0} ", where), pageSize);
            pdes.CurrentPage = currentPage;
            totalRecord = pdes.TotalCount;

            DataTable dt = pdes.GetSpecailPage();
            return HZ.Data.DAL_Helper.CommonFillList<HZ.DrugTrade.Model.View.ProcureCatalog>(dt);
        }

        ///// <summary>
        ///// 新增采购目录
        ///// </summary>
        ///// <param name="procureCatalog">待插入的采购目录</param>
        ///// <returns>成功与否</returns>
        //public static int AddProcureCatalog(ProcureCatalog procureCatalog)
        //{
        //    int success = 0;
        //    SqlParameter[] paras = new SqlParameter[13];
        //    paras[0] = new SqlParameter("@ProductName", procureCatalog.ProductName);
        //    paras[1] = new SqlParameter("@MedicineModelID", procureCatalog.MedicineModelID);
        //    paras[2] = new SqlParameter("@Outlookc", procureCatalog.Outlookc);
        //    paras[3] = new SqlParameter("@Unit", procureCatalog.Unit);
        //    paras[4] = new SqlParameter("@Factor", procureCatalog.Factor);
        //    paras[5] = new SqlParameter("@Pack", procureCatalog.Pack);
        //    paras[6] = new SqlParameter("@GoodsName", procureCatalog.GoodsName);
        //    paras[7] = new SqlParameter("@SortID", procureCatalog.SortID);
        //    paras[8] = new SqlParameter("@CompanyID_SC", procureCatalog.CompanyID_SC);
        //    paras[9] = new SqlParameter("@CompanyID_TB", procureCatalog.CompanyID_TB);
        //    paras[10] = new SqlParameter("@ProcurePrice", procureCatalog.ProcurePrice);
        //    paras[11] = new SqlParameter("@CompanyID_PS_List", procureCatalog.CompanyID_PS_List);
        //    paras[12] = new SqlParameter("@Success", success);
        //    paras[12].Direction = ParameterDirection.Output;


        //    HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_ABC", paras);
        //    return success;
        //}

        /// <summary>
        /// 获取一条采购目录
        /// </summary>
        /// <param name="procureCatalogID">采购目录编号</param>
        /// <returns></returns>
        public static HZ.DrugTrade.Model.View.ProcureCatalog GetProcureCatalogByID(string procureCatalogID)
        {
            string sql = string.Format("SELECT  * FROM View_ProcureCatalog_tb   WHERE rownum<=1 and ProcureCatalogID = '{0}'",procureCatalogID);
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);
            if (dt.Rows.Count == 0)
            {
                return null;
            }
            return HZ.DrugTrade.DAL2.DAL_Helper.CommonFill<HZ.DrugTrade.Model.View.ProcureCatalog>(dt.Rows[0]);
        }


        /// <summary>
        /// 删除一个目录
        /// </summary>
        /// <param name="procureCatalogID">采购目录ID</param>
        /// <returns>1成功 0 失败</returns>
        public static int DeleteProcureCatalogByID(string procureCatalogID)
        {
            SqlParameter[] paras = new SqlParameter[] 
            {
                new SqlParameter("@in_ProcureCatalogID", procureCatalogID),
                new SqlParameter("@out_success",SqlDbType.Int)
            };
            paras[paras.Length - 1].Direction = ParameterDirection.Output;

            HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_ProcureCatalog_Delete", paras);

            return (int)paras[paras.Length - 1].Value;
        }


        #region 调价计划

      
        /// <summary>
        /// 删除调价计划（0.不成功,1.成功,2.调价计划编号不存在）
        /// </summary>
        /// <param name="planID">调价计划编号</param>
        /// <returns>0失败;1成功</returns>
        public static int DeleteAdjustPricePlanByID(string planID)
        {
            SqlParameter[] paras = new SqlParameter[] 
            {
                new SqlParameter("@in_PricePlanSerialID", planID),
                new SqlParameter("@out_success",SqlDbType.Int)
            };
            paras[paras.Length - 1].Direction = ParameterDirection.Output;

            HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_AdjustPricePlan_Del", paras);

            return (int)paras[paras.Length - 1].Value;
        }
        /// <summary>
        /// 条件查询调价计划信息
        /// </summary>
        /// <param name="plan">查询用实体</param>
        /// <param name="currentPage">当前页</param>
        /// <param name="pageSize">每页显示记录数</param>
        /// <param name="totalRecord">（输出参数）总记录数目</param>
        /// <returns>所有采购目录信息</returns>
        public static IList<HZ.DrugTrade.Model.View.AdjustPricePlan> GetAdjustPricePlanByWhere(HZ.DrugTrade.Model.Query.AdjustPricePlan plan, int currentpage, int pagesize, out int totalrecord)
        {
            string where = HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition<HZ.DrugTrade.Model.Query.AdjustPricePlan>(plan,true);

            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(string.Format("SELECT * FROM View_AdjustPricePlan WHERE 1=1 {0} ", where), pagesize);
            pdes.CurrentPage = currentpage;
            totalrecord = pdes.TotalCount;

            DataTable dt = pdes.GetSpecailPage();
            return HZ.Data.DAL_Helper.CommonFillList<HZ.DrugTrade.Model.View.AdjustPricePlan>(dt);
        }
        /// <summary>
        /// 条件查询调价计划信息表 add by zdc 2011-10-21
        /// </summary>
        /// <param name="plan">查询用实体</param>
        /// <param name="currentPage">当前页</param>
        /// <param name="pageSize">每页显示记录数</param>
        /// <param name="totalRecord">（输出参数）总记录数目</param>
        /// <returns>所有采购目录信息</returns>
        public static System.Data.DataTable GetAdjustPricePlanDTByWhere(HZ.DrugTrade.Model.Query.AdjustPricePlan plan, int currentpage, int pagesize, out int totalrecord)
        {
            string where = HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition<HZ.DrugTrade.Model.Query.AdjustPricePlan>(plan, true);

            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(string.Format("SELECT * FROM View_AdjustPricePlan WHERE 1=1 {0} ", where), pagesize);
            pdes.CurrentPage = currentpage;
            totalrecord = pdes.TotalCount;

            DataTable dt = pdes.GetSpecailPage();
            return dt;
        }


        /// <summary>
        /// 获取调价记录 zdc
        /// </summary>
        /// <param name="procurecatalogId">采购目录编号</param>
        /// <param name="currentPage">当前页</param>
        /// <param name="pageSize">每页显示数</param>
        /// <param name="totalRecord">总数</param>
        /// <returns></returns>
        public static IList<Model.AdjustPricePlan> GetAdjustPricePlanByWhere(string procurecatalogId, int currentPage, int pageSize, out int totalRecord)
        {
            //Model.AdjustPricePlan adjust = new AdjustPricePlan();
            //adjust.ProcureCatalogID = procurecatalogId;
            //string where = HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition<Model.AdjustPricePlan>(adjust);

            string sql = string.Format("select * from TB_AdjustPricePlan where 1=1    AND ProcureCatalogID = '{0}'order by executestarttime desc", procurecatalogId);
            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(sql, pageSize);
            pdes.CurrentPage = currentPage;
            totalRecord = pdes.TotalCount;
            DataTable dt = pdes.GetSpecailPage();
            return HZ.DrugTrade.DAL2.DAL_Helper.CommonFillList<HZ.DrugTrade.Model.AdjustPricePlan>(dt);
        }


        /// <summary>
        /// 获取一条调价目录 zdc
        /// </summary>
        /// <param name="procureCatalogID">采购目录编号</param>
        /// <returns></returns>
        public static HZ.DrugTrade.Model.AdjustPricePlan GetAdjustPricePlanByID(string procureCatalogID)
        {
            //string sql = string.Format("select  * from TB_AdjustPricePlan where rownum<=1 and ProcureCatalogID='{0}' order by executestarttime desc", procureCatalogID);
            string sql=string.Format(@"select * from (select ROW_NUMBER() OVER(ORDER BY executestarttime DESC) AS pm,
                        a.*  from TB_AdjustPricePlan  a where  ProcureCatalogID='{0}')b where pm=1", procureCatalogID);
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);
            if (dt.Rows.Count == 0)
            {
                return null;
            }
            return HZ.DrugTrade.DAL2.DAL_Helper.CommonFill<HZ.DrugTrade.Model.AdjustPricePlan>(dt.Rows[0]);
        }
        #endregion
        /// <summary>
        /// 根据机构ID判断是否基层医疗机构 add by zdc
        /// </summary>
        /// <param name="orgID"></param>
        /// <returns></returns>
        public static bool GetIsJiCeng(string orgID)
        {
            bool flag = false;
            string sqlInfo = string.Format("select countyOver from tb_HospitalInfo where hospitalid='"+orgID+"'");
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(sqlInfo);
            if (dt.Rows[0][0].ToString()=="0")//是基层医疗机构
            {
                flag = true;
            }
            return flag;
        }

        public static int SubmitProcureCatalogList(string GoodsId, int flag, string NoPassReson,string UserName)
        {
            int s = 0;
            if (flag == 0)
            {
                string sql = string.Format("Update VIEW_PROCURECATALOG_TB set SubmitStatus=1 where GoodsId={0}", GoodsId);
                HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(string.Format("Update TB_GOODS set SubmitStatus=1 where GoodsId={0}", GoodsId));
                s = HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(sql);
            }
            if (flag == 1)
            {
                string sql = string.Format("Update VIEW_PROCURECATALOG_TB set CheckStatus=1,AUDITUSERNAME='{1}' where GoodsId={0} ", GoodsId, UserName);
                HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(string.Format("Update TB_GOODS set CheckStatus=1,AUDITUSERNAME='{1}' where GoodsId={0}", GoodsId,UserName));
                s = HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(sql);
            }
            if (flag == 2)
            {
                string sql = string.Format("Update VIEW_PROCURECATALOG_TB set CheckStatus=2,NoPassReson='{1}',AUDITUSERNAME='{2}' where GoodsId={0}", GoodsId, NoPassReson,UserName);
                HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(string.Format("Update TB_GOODS set CheckStatus=2,NoPassReson='{2}',AUDITUSERNAME='{1}' where GoodsId={0}", GoodsId, NoPassReson,UserName));
                s = HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(sql);
            }
            return s;
        }
        public static int UpdateProcureCatalogList(Model.View.ProcureCatalog list)
        {
            int flag=0;
            string sql = string.Format("Update VIEW_PROCURECATALOG_TB set PackUnit='{0}',PackCount='{1}' where GoodsId={2}", list.PackUnit, list.PackCount, list.GoodsID);
            HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(string.Format("Update TB_GOODS set PackUnit='{0}',PackCount='{1}' where GoodsId={2}", list.PackUnit, list.PackCount, list.GoodsID));
            flag = HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(sql);
            return flag;
        }
            
    }
}
